from django.shortcuts import render, redirect, HttpResponse
import pymysql
import json
import redis
import jieba
import threading
import random

# Create your views here.
# 创建MySQL连接
mysql_conn = pymysql.connect(host='127.0.0.1', user='root', password='123456', db='msquare', charset='utf8mb4')
cursor = mysql_conn.cursor(pymysql.cursors.DictCursor)

# 创建redis连接
redis_conn = redis.Redis(host='127.0.0.1', port=6379)

lock = threading.Lock()


def ping_mysql():
    new_conn = pymysql.connect(host='127.0.0.1', user='root', password='123456', db='msquare', charset='utf8mb4')
    new_cursor = mysql_conn.cursor(pymysql.cursors.DictCursor)

    return new_conn, new_cursor

#个性化推荐的电影
def recommend_movie(request):
    '''

    :param request: userid
    :return: result: 为用户推荐的电影
    '''
    mysql_conn, cursor = ping_mysql()
    mysql_conn.ping(reconnect=True)
    result = {'status': 200}
    if request.method == 'GET':
        lock.acquire()
        user_id = request.GET.get('user_id')
        sql = "select recommend_id from user_t_recomment where user_id='%s'" %user_id
        try:
            cursor.execute(sql)
            data = cursor.fetchall()
            if data:
                res = data[0]['recommend_id'].split('-')
            else:
                res = []
            if len(res)<10:
                res += [random.randint(1,1342) for i in range(10-len(res))]
            result['info'] = []
            for i in range(10):
                sql = "select * from movie_t_movie where id='%s'" %res[i]
                cursor.execute(sql)
                data = cursor.fetchall()
                result['info'].append(data[0])
            lock.release()
        except:
            print(sql)
            result['status'] = 500
    mysql_conn.close()
    return HttpResponse(json.dumps(result))

#精选电影
def choice_movie(request):
    '''

    :param request:
    :return: result: 电影/图片信息
    '''
    mysql_conn, cursor = ping_mysql()
    mysql_conn.ping(reconnect=True)
    result = {'status': 200}
    if request.method == 'GET':
        lock.acquire()
        sql = 'select * from movie_t_movie order by score DESC LIMIT 9'
        try:
            cursor.execute(sql)
            lock.release()
            data = cursor.fetchall()
            result['info'] = data
        except:
            print(sql)
            result['status'] = 500
    mysql_conn.close()
    return HttpResponse(json.dumps(result))

#其它电影
def other_movies(request):
    '''

    :param request: movie_id：电影id
    :return: 返回类似电影
    '''
    result = {'status': 200}
    if request.method == 'GET':
        movie_id = request.GET.get('movie_id')
        mysql_conn = pymysql.connect(host='127.0.0.1', user='root', password='123456', db='msquare', charset='utf8mb4')
        cursor = mysql_conn.cursor(pymysql.cursors.DictCursor)
        try:
            i = random.randint(0,50)
            sql = "select distinct movie_id from movie_t_movie_class m1, movie_t_movie m2 where m1.target_id in (SELECT target_id from movie_t_movie_class where movie_id = '%s') and m1.movie_id=m2.id and m2.id!='%s' order by m2.score DESC LIMIT %d,9" %(movie_id,movie_id,i)
            cursor.execute(sql)
            movies = cursor.fetchall()
            result['info'] = []
            for item in movies:
                id = item['movie_id']
                sql = "select id,movie_name,score,url from movie_t_movie where id='%s'" %id
                cursor.execute(sql)
                data = cursor.fetchall()
                result['info'].append(data[0])
        except:
            print(sql)
            result['status'] = 500
        mysql_conn.close()
    return HttpResponse(json.dumps(result))

#返回电影名句
def movie_lines(request):
    '''

    :param request: movie_id：电影id
    :return: 返回电影名句
    '''
    result = {'status': 200}
    if request.method == 'GET':
        movie_id = request.GET.get('movie_id')
        mysql_conn = pymysql.connect(host='127.0.0.1', user='root', password='123456', db='msquare', charset='utf8mb4')
        cursor = mysql_conn.cursor(pymysql.cursors.DictCursor)
        try:
            sql = "select line from movie_t_movie_lines where movie_id='%s'" %movie_id
            cursor.execute(sql)
            data = cursor.fetchall()
            result['info'] = data
        except:
            print(sql)
            result['status'] = 500
    return HttpResponse(json.dumps(result))

#电影详情页
def movie_show(request):
    '''
    :param request: movie_id:电影id
    :return: 跳转到电影详情页
    '''
    if request.method == 'GET':
        content = {'opacity': 0, 'box_office': 0, 'buy_url': '#'}
        user_id = request.session.get('user_id',-1)
        movie_id = request.GET.get('movie_id')
        mysql_conn = pymysql.connect(host='127.0.0.1', user='root', password='123456', db='msquare', charset='utf8mb4')
        cursor = mysql_conn.cursor(pymysql.cursors.DictCursor)

        # 判断该电影是否在热映表中
        sql = "select box_office, buy from movie_t_new_movie where movie_id=%s" % movie_id
        try:
            cursor.execute(sql)
            new_info = cursor.fetchone()
            if new_info:
                content.update({
                    'box_office': new_info['box_office'],
                    'opacity': 1,
                    'buy_url': new_info['buy'],
                })
        except Exception as e:
            print('movie_show: ', sql, e)

        sql = "select * from movie_t_movie where id='%s'" % movie_id
        try:
            cursor.execute(sql)
            data = cursor.fetchall()
            temp = data[0]
            content.update({
                'movie_id': temp['id'],
                'movie_name': temp['movie_name'],
                'country': temp['country'],
                'release_date': temp['release_date'],
                'score': temp['score'],
                'url': temp['url'],
                'directors': temp['directors'],
                'information': temp['information'],
            })
        except Exception as e:
            print('movie_show: ', sql, e)


        sql = "select target from movie_t_movie_target m1,movie_t_movie_class m2 where m1.id=m2.target_id and m2.movie_id='%s'" % movie_id
        cursor.execute(sql)
        data = cursor.fetchall()
        mysql_conn.close()
        targets = []
        for i in data:
            targets.append(i['target'])
        target = ','.join(targets)
        content.update({
            'target': target,
            'user_id': user_id
        })

        return render(request,'movie/movie_single.html', content)


def home(request):
    pass


def hot_movie(request):
    '''

    :param request: type(0：热映电影，1：热门电影)；
    :return: result：电影信息
    '''
    # 返回正在热映和热门电影信息
    mysql_conn, cursor = ping_mysql()
    mysql_conn.ping(reconnect=True)
    result = {'status': 200}
    if request.method == 'GET':
        if request.GET.get('type') == '0':
            sql = 'select * from movie_t_new_movie m1,movie_t_movie m2 where m1.movie_id = m2.id order by box_office DESC'
        else:
            sql = "select * from movie_t_movie order by (parise_num*2+star_num*5+click_num) DESC LIMIT 10"
        # try:
        lock.acquire()
        cursor.execute(sql)
        lock.release()
        data = cursor.fetchall()
        result['info'] = data
        # except:
        #     print(sql)
        result['status'] = 500
    mysql_conn.close()
    return HttpResponse(json.dumps(result))


def filter_media(request):
    '''

    :param request: type（0：电影筛选，1：图片筛选）；target（标签）
    :return: 电影/图片信息
    '''

    result = {'status': 200}
    if request.method == 'GET':
        target = request.GET.get('target')
        if request.GET.get('type') == '0':
            sql = "select movie_id from movie_t_movie_class where target='%s'" % target
        else:
            sql = "select picture_id from picture_t_picture_class where target='%s'" % target
        try:
            cursor.execute(sql)
            data = cursor.fetchall()
            result['info'] = data
        except:
            result['status'] = 500
    return HttpResponse(json.dumps(result))


def info_media(request):
    '''

    :param request: type（0：电影，1：图片）；id（要获取的信息的id）
    :return: 具体电影/图片信息
    '''

    result = {'status': 200}
    if request.method == 'GET':
        if request.GET.get('type') == '0':
            movie_id = request.GET.get('mid')
            sql = "select * from movie_t_movie where id = '%s'" % movie_id
        else:
            pic_id = request.GET.get('pid')
            sql = "select * from picture_t_picture where id = '%s'" % pic_id
        try:
            lock.acquire()
            cursor.execute(sql)
            lock.release()
            data = cursor.fetchone()
            result['info'] = data
            mysql_conn.commit()
        except:
            result['status'] = 500
    return HttpResponse(json.dumps(result))


def rangs_media(request):
    '''

    :param request: type（0：电影，1：图片）
    :return: 热度前十的电影/图片
    '''
    # 电影/图片 排行榜
    mysql_conn, cursor = ping_mysql()
    result = {'status': 200}
    if request.method == 'GET':
        if request.GET.get('type') == '0':
            # 电影排行榜
            sql = "select * from movie_t_movie order by (parise_num*2+star_num*5+click_num) DESC LIMIT 10"
        else:
            # 图片排行榜
            sql = "select * from picture_t_picture order by (parise_num*2+star_num*5+click_num) DESC LIMIT 10"
        try:
            cursor.execute(sql)
            data = cursor.fetchall()
            result['info'] = data
        except:
            result['status '] = 500
    mysql_conn.close()
    return HttpResponse(json.dumps(result))


# 获取部分电影
def get_movies(request):
    '''

    :param request: 要求的电影数量、用户的ID
    :return: 电影概略信息
    '''
    result = {'status': 200}
    num = int(request.GET.get('num', 0))
    user_id = request.session.get('user_id', None)

    # 如果用户登录
    if user_id:
        # 将用户传入推荐系统，返回用户喜欢的4个类别
        targets = []
        pass

        # 从redis中查取符合4个类别的所有movie_id
        movies_id = redis_conn.sinter(targets)
        movies_id = tuple(map(lambda x: int(x.decode()), movies_id | {-1, -2}))

        # 查询所有movie_id,返回指定数量
        sql = "select * from movie_t_movie where id in %s limit %d, %d" % (repr(movies_id), num, 30)
    else:
        sql = "select * from movie_t_movie limit %d, %d" % (num, 30)
    try:
        cursor.execute(sql)
        movies_info = cursor.fetchall()
        result['info'] = movies_info
    except:
        print(sql)
        result['status'] = 500

    return HttpResponse(json.dumps(result))


# 获取电影全部标签
def get_targets(request):
    '''

    :param request: 无
    :return: 电影全部标签
    '''
    result = {'status': 200, 'info': {'types': [], 'country': [], 'release': []}}

    sql = "select * from movie_t_movie_target"
    try:
        cursor.execute(sql)
        targets_info = cursor.fetchall()
        for each in targets_info:
            if each['type'] == 0:
                result['info']['types'].append(each)
            elif each['type'] == 1:
                result['info']['country'].append(each)
            else:
                result['info']['release'].append(each)
    except:
        print(sql)
        result['status'] = 500

    return HttpResponse(json.dumps(result))


# 热门电影评论
def hot_movie_comment(request):
    '''

    :param request: num
    :return: 电影ID 电影封面 电影名称 评论ID 用户ID 用户名 评论时间 评论信息
    '''

    # 从请求中取出当前评论数量
    num = int(request.GET.get('num', 0))

    # 查询热门电影评论，以点赞数排序
    sql = 'select t1.id movie_id, t1.url image_url, t1.movie_name, t2.id comment_id, t2.username, t2.user_id, ' \
          't2.time, t2.content from movie_t_movie t1, user_t_comment t2 where t1.id = t2.media_id and t2.type = 0 ' \
          'and t2.reply_id = -1 order by t2.parise_num desc limit %d,%d' % (num, 5)
    result = {'status': 200}
    comment_info = {}
    try:
        cursor.execute(sql)
        comment_info = cursor.fetchall()
        for item in comment_info:
            item['time'] = str(item['time'])
    except Exception as e:
        result['status'] = 403
        print(e)
    result['info'] = comment_info
    return HttpResponse(json.dumps(result))


# 电影详情页评论--时间排序
def movie_comment_time(request):
    '''

    :param request: num, movie_id
    :return: 评论ID 用户ID 电影ID 用户头像 用户名 评论时间 评论内容 点赞数 评论数
    '''

    # 从请求中取出电影id，当前评论数量
    movie_id = int(request.GET.get('movie_id'))
    num = int(request.GET.get('num', 0))

    # 查询电影评论，返回指定数量
    sql = 'select t2.id comment_id, t2.user_id, t1.id movie_id, t3.avatar, t3.name, t2.time, t2.content, ' \
          't2.parise_num, t2.comment_num from movie_t_movie t1, user_t_comment t2, user_t_user t3 where t1.id = ' \
          't2.media_id and t2.type = 0 and t3.id = t2.user_id and t2.reply_id = -1 and t2.media_id = %d order by ' \
          't2.time desc limit %d,%d' % (movie_id, num, num + 5)
    result = {'status': 200}
    comment_info = {}
    try:
        cursor.execute(sql)
        comment_info = cursor.fetchall()
        for item in comment_info:
            item['time'] = str(item['time'])
    except:
        result['status'] = 403
    result['info'] = comment_info
    return HttpResponse(json.dumps(result))


# 电影详情页评论--点赞排序
def movie_comment_parise(request):
    '''

    :param request: num, movie_id
    :return: 评论ID 用户ID 电影ID 用户头像 用户名 评论时间 评论内容 点赞数 评论数
    '''

    # 从请求中取出电影id，当前评论数量
    movie_id = int(request.GET.get('movie_id'))
    num = int(request.GET.get('num', 0))

    # 查询电影评论，返回指定数量
    sql = 'select t2.id comment_id, t2.user_id, t1.id movie_id, t3.avatar, t3.name, t2.time, t2.content, ' \
          't2.parise_num, t2.comment_num from movie_t_movie t1, user_t_comment t2, user_t_user t3 where t1.id = ' \
          't2.media_id and t2.type = 0 and t3.id = t2.user_id and t2.reply_id = -1 and t2.media_id = %d order by ' \
          't2.parise_num desc limit %d,%d' % (movie_id, num, 5)
    result = {'status': 200}
    comment_info = {}
    try:
        lock.acquire()
        new_conn, new_cursor = ping_mysql()
        new_cursor.execute(sql)
        lock.release()
        comment_info = new_cursor.fetchall()
        for item in comment_info:
            item['time'] = str(item['time'])
    except Exception as e:
        result['status'] = 403
    result['comment_info'] = comment_info

    return HttpResponse(json.dumps(result))


# 电影详情页评论回复
def movie_comment_reply(request):
    '''

    :param request: num, comment_id
    :return: 评论回复ID 用户ID 电影ID 用户头像 用户名 评论时间 评论内容 点赞数 评论数
    '''
    comment_id = int(request.GET.get('comment_id'))
    num = int(request.GET.get('num', 0))

    # 查询电影评论回复，返回指定数量
    sql = 'select t2.id comment_reply_id, t2.reply_id, t2.user_id, t1.id movie_id, t3.avatar, t3.name, t2.time, t2.content, ' \
          't2.parise_num, t2.comment_num from movie_t_movie t1, user_t_comment t2, user_t_user t3 where t1.id = ' \
          't2.media_id and t2.type = 0 and t3.id = t2.user_id and t2.reply_id = %d order by t2.time desc ' \
          'limit %d,%d' % (comment_id, num, 5)
    result = {'status': 200}
    comment_info = {}
    try:
        lock.acquire()
        new_conn, new_cursor = ping_mysql()
        new_cursor.execute(sql)
        lock.release()
        comment_info = new_cursor.fetchall()
        for item in comment_info:
            item['time'] = str(item['time'])
    except Exception as e:
        print(e)
        result['status'] = 403
    result['reply_info'] = comment_info

    return HttpResponse(json.dumps(result))


# 图片搜索结果页面
def search(request):
    content = request.GET.get('content', '')
    return render(request, 'movie/search.html', {'content': content})

